package uk.co.argos.ecommerce.dataload.extract;

import java.io.BufferedWriter;
import java.io.Closeable;
import java.io.File;
import java.io.FileWriter;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;

import com.ibm.commerce.util.nc_crypt;

public abstract class ExtractProcess {

	protected Logger log;
	protected Timestamp now = new Timestamp((new Date()).getTime());
	
	private PropertyHelper ph;
	private Connection dbConn;
	private PrintWriter printer; 
	private String rootDir;
	private PreparedStatement statement;
	private ResultSet results;
	private int commitInterval;
	
	private SimpleDateFormat df = new SimpleDateFormat("yyyyMMdd-HHmmss");
	
	private class Range {
		private Range(Object fr) {
			from	= fr;
			to		= fr; 
		}
		Object from;
		Object to;
	}
	
	private class ExtractIO {

		protected String property_db_user;
		protected String property_db_password;
		protected String property_extracts_root_dir;
		protected String property_db_driver_name;
		protected String property_db_server;
		protected String property_db_port;
		protected String property_db_name;
		protected String property_commit_interval = "500";
		
	}
	
	protected class ExtractStore {
		
		protected int id;
		private String name;
		private String subdir;
		protected String property_run;
		protected String property_DaysAhead;
		protected String property_DaysRange;
		protected String property_option1 = "";
		protected String property_option2 = "";
		protected String property_option3 = "";
		protected Timestamp from;
		protected Timestamp to;
		private File destination;
		
		private boolean hasRunFlag() {
			return Boolean.valueOf(property_run);
		}

		public String toString() {

			return 
			"\n"+name+" ("+id+")"+
			"\ndestination="+destination+
			"\nfrom="+from+
			"\nto="+to;
		}
	}
	
	protected static void runMe(String... p) {

		ExtractProcess process = null;
		StackTraceElement[] st = (new Throwable()).getStackTrace();
		String mainClass = st[1].getClassName();
		int rc = 0;

		try {
			process = (ExtractProcess) Class.forName(mainClass).newInstance();
			process.start(p);
		} 
		catch (Exception e) {
			rc = fail(process, mainClass, e);
		}
		finally {
			if (process != null) process.end(rc);
		}

	}
	
	private void start(String... p) throws Exception {
		
		init(p);
		for (ExtractStore store : getStores() ) {
			initStore(store);
			processStore(store);
		}
		
	}
	
	private void initStore(ExtractStore store) throws Exception {
		
		File dir = checkDirs(store);
		store.destination = new File(dir, getRegistryTag().toLowerCase()+"_"+df.format(new Date())+".csv");
		store.from = createMidnightTimestamp(store.property_DaysAhead);
		store.to = createMidnightTimestamp(store.property_DaysAhead, store.property_DaysRange);
		close(printer);
		printer = new PrintWriter(new BufferedWriter(new FileWriter(store.destination)));
		log.info("\n\nStore initialised\n"+store+"\n\n");
		
	}

	private File checkDirs(ExtractStore store) {
		File dir = new File(rootDir, store.subdir);
		if (dir.exists()) {
			log.info(getRegistryTag()+": output directory found");
		}
		else {
			log.warning(getRegistryTag()+": output directory not found, creating...");			
			dir.mkdirs();
		}
		return dir;
	}

	protected void print(String... detail) {
		
		if (detail == null || detail.length == 0) {
			printer.println();
		}
		else {
			printer.print(detail[0]);
		}
		
	}

	protected abstract String getRegistryTag();
	
	private static int fail(ExtractProcess runner, String cn, Exception e) {
		
		String msg = failBanner()+"\nClass: "+cn+"\nInstance: "+runner;
		if (runner != null && runner.log != null) {
			runner.log.log(Level.SEVERE, msg, e);
		} 
		else {
			throw new RuntimeException(msg, e);
		}
		return 1;

	}

	private static String failBanner() {

		return 
		
		"\n\t+---------------------------------+"+		
		"\n\t|   E X T R A C T   F A I L E D   |"+
		"\n\t+---------------------------------+"+		
		"\n";		
		
	}

	private void init(String... p) throws Exception {

		log = Logger.getLogger(this.getClass().getName());
		ph = new PropertyHelper(log);
		checkParameters(p);
		initIO(p);
		signin();
	}

	private void signin() {
		String msg = 
			"\n\n"+
			"---\n"+
			"--- Extract Report ("+getRegistryTag()+")\n"+
			"---\n"+
			"--- Initialised, ready to run\n"+
			"---\n";
		log.info(msg);
	}
	
	protected void printField(Object field, boolean... isLast) {
		
		if (field == null || field == "-") {
			print("\"\"");
		}
		else {
			print('"'+field.toString().replace('"', '`').replace(',', ':')+'"');
		}
		if (isLast == null || !isLast[0]) { 
			print(", ");		
		}
	}	

	private void initIO(String... p) throws Exception {
		
		ExtractIO io = new ExtractIO();
		Properties props = ph.getPropertiesFromSource(new File(p[0]));
		ph.initVarsFromProperties(io, props, true);
		Class.forName(io.property_db_driver_name);
		String url = "jdbc:db2://"+io.property_db_server+":"+io.property_db_port+"/"+io.property_db_name;
		dbConn = DriverManager.getConnection(url, io.property_db_user, decrypt(io.property_db_password));
		rootDir = io.property_extracts_root_dir;
		commitInterval = Integer.valueOf(io.property_commit_interval);
		
	}

	private String decrypt(String text) throws Exception {
		String decrypted = nc_crypt.decrypt(text, null);
		if (decrypted == null) {
			throw new Exception("Cannot decrypt password: "+text);
		}
		else {
			log.info(text+"("+decrypted+")");
		}
		return decrypted;
	}

	private void checkParameters(String... p) throws Exception {
		
		if(p.length == 1) {
			log.info("property file = "+p[0]);
		}
		else {
			throw new Exception("Parameter count should be 1(one) not "+p.length);
		}
		
	}

	protected abstract void processStore(ExtractStore store) throws Exception;

	private void end(int rc) {
		
		try {
			close(results, statement, dbConn, printer);
		}
		catch (Exception e) {
			throw new RuntimeException("#end - close failure", e);
		}
		finally {
			signout(rc);
			System.exit(rc);
		}
	}

	private void signout(int rc) {
		String msg = 
			"\n\n"+
			"---\n"+
			"--- Extract Report ("+getRegistryTag()+")\n"+
			"---\n"+
			"--- Completeted with return code "+rc+".\n"+
			"---\n";
		log.info(msg);
	}

	private Collection<ExtractStore> getStores() throws Exception {
		
		String sql = "select s.store_id, e.identifier, s.directory, x.value from ecxreg x, store s, storeent e where name = ? and int(x.STOREID) = s.STORE_ID and s.STORE_ID = e.STOREENT_ID";
		Collection<ExtractStore> stores = new ArrayList<ExtractStore>();
		executeQuery(sql, getRegistryTag());
		while (results.next()) {
			int i = 1;
			ExtractStore store = newExtractStore();
			store.id = results.getInt(i++);
			store.name = results.getString(i++);
			store.subdir = results.getString(i++);
			Properties props = ph.getPropertiesFromSource(results.getString(i++));
			ph.initVarsFromProperties(store, props, true);
			if (store.hasRunFlag()) {
				stores.add(store);
			}
		}
		return stores;

	}

	protected ExtractStore newExtractStore() {

		return new ExtractStore();
	}

	private Object executeSQL(boolean isDml, String sql, Object... parms) {
		
		Object o = null;
		try {
			close(results, statement);
			statement = dbConn.prepareStatement(sql);
			loadPSValues(statement, parms);
			o = isDml ? statement.executeUpdate() : statement.executeQuery();
		} 
		catch (Exception e) {
			log.severe(" SQL Execution error: "+e);
			throw new RuntimeException(e);
		}
		return o;
				
	}
	
	protected int executeSQLDml(String sql, Object... parms) {
		return (Integer) executeSQL(true, sql, parms); 
	}
	
	
	protected int executeIntervalUpdate(String updatePrefix, String keySelectionSQL, Object... keySelectionParms) throws Exception {

		String sql = keySelectionSQL+" order by 1 with ur"; 
		int rows = 0;

		results = executeQuery(sql, keySelectionParms);
		List<Range> ranges = new ArrayList<Range>();
		addRanges(commitInterval, ranges);
		sql = updatePrefix+" between ? and ? ";
		for (Range range : ranges) {
			rows += executeSQLDml(sql, range.from, range.to);
		}
		return rows;
		
	}

	private void addRanges(int rangeGap, List<Range> ranges) throws Exception {
		
		Range range = null;
		int n = 0;
		while (results.next()) {
			if (n++%rangeGap == 0) {
				range = new Range(results.getObject(1));
				ranges.add(range);
			}
			else {
				range.to = results.getObject(1);
			}
		}
				

	}
	

	protected ResultSet executeQuery(String sql, Object... parms) {
		results = (ResultSet) executeSQL(false, sql, parms);
		return results; 
	}

	private void close(Object... toClose) throws Exception {

		for (Object obj : toClose ) {
			if 		(obj instanceof Closeable)((Closeable)obj).close();
			else if (obj instanceof Statement)((Statement)obj).close();
			else if (obj instanceof ResultSet)((ResultSet)obj).close();
			else if (obj instanceof Connection)((Connection)obj).close();			
			else if (obj != null) 
				throw new RuntimeException("#close Unknown Object to close:"+obj);
		}
		
	}
	
	private void loadPSValues(PreparedStatement st, Object... parms) throws Exception {

		int i = 1;
		for ( Object obj : parms) {
			if (obj instanceof String) 			st.setString(i++, obj.toString());	
			else if (obj instanceof Integer ) 	st.setInt(i++, (Integer)obj);
			else if (obj instanceof Timestamp ) st.setTimestamp(i++, (Timestamp)obj);
			else throw new Exception("Parameter type not known:"+obj);
		}
	}

	private Timestamp createMidnightTimestamp(String... vals) {

		long offset = 0;
		for (String val : vals) offset += Long.valueOf(val);
		long newDate = (new Date()).getTime()+(offset*86400000);
		return Timestamp.valueOf(new java.sql.Date(newDate)+" 00:00:00");

	}
	
	protected void createSimpleExtractReport(String sql, Object... parms) throws Exception {
		
		executeQuery(sql, parms);
		ResultSetMetaData md = results.getMetaData();	
		int lastCol = md.getColumnCount();
		
		while (results.next()) {
			for ( int col=1; col <= md.getColumnCount(); col++) {
				Object obj = results.getObject(col); 
				printField(obj, col==lastCol);
			}
			print();
		}

	}
	
	protected String readOption(ExtractStore store, String tagSuff) throws Exception {
	
		String sql = "select value from ecxreg where name = ? storeid = ?";
		executeQuery(sql, String.valueOf(store.id), updatableTag(tagSuff) );
		results.next();
		return results.getString(1);
	}
	
	private String updatableTag(String tagSuff) {
		return getRegistryTag()+"_UPDATABLE_"+tagSuff;
	}
	
	
	protected void updateOption(ExtractStore store, String tagSuff, String value) throws Exception {
		
		String sql = "update ecxreg set value = ? where name = ? storeid = ?";
		executeSQLDml(sql, value, String.valueOf(store.id), updatableTag(tagSuff) );
		
	}
	
 
	

}
